﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Caching;
using Common;

using System.IO;
using System.Data.OleDb;

namespace DAL
{
    public class Stock
    {
        public Dat_Stock SelectStock(int StockKey)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_Stock stock = new Dat_Stock();

            try
            { return db.Dat_Stock.Single(s => s.StockKey == StockKey); }
            catch (Exception ex)
            { return stock;  }
        }

         //条件查询
        public IQueryable<Dat_Stock> SelectStock(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_Stock> Query;

            if (QueryTable == null)
            {
                Query = from s in db.Dat_Stock
                        orderby s.StockKey descending
                        select s;
            }
            else
            {
                Query = from s in GetConditionQuery(QueryTable)
                        orderby s.StockKey descending
                        select s;
            }
            return Query;
        }

        private IQueryable<Dat_Stock> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();



            var predicate = PredicateBuilder.True<Dat_Stock>();

            if (QueryTable["StName"] != null)
            {
                predicate = predicate.And(s => s.StName.Contains(QueryTable["StName"].ToString().Trim()));
            }
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And(s => s.StSpec.Contains(QueryTable["Keyword"].ToString().Trim()) ||
                   s.Price.ToString().Contains(QueryTable["Keyword"].ToString().Trim()) ||
                   s.Num.ToString().Contains(QueryTable["Keyword"].ToString().Trim()) );
            if (QueryTable["OneDate"] != null)
            {
                try
                {
                    DateTime OneDate = Convert.ToDateTime(QueryTable["OneDate"]);
                    predicate = predicate.And(i => i.CreateTime >= OneDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["TwoDate"] != null)
            {
                try
                {
                    DateTime TwoDate = Convert.ToDateTime(QueryTable["TwoDate"].ToString().Split(' ')[0] + " 23:59:59");
                    predicate = predicate.And(i => i.CreateTime <= TwoDate);
                }
                catch (Exception ex) { }
            }

            return db.Dat_Stock.Where(predicate);
        }

        public bool AddStock(Dat_Stock stock)
        {
            bool Success;
            CIMDataContext db = new CIMDataContext();

            db.Dat_Stock.InsertOnSubmit(stock);

            try
            {
                db.SubmitChanges();
                Success =  true;
            }
            catch(Exception ex)
            {
                Success =  false;
            }
            return Success;
        }

        public bool EditStock(Dat_Stock stock)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_Stock Stock = db.Dat_Stock.Single(s => s.StockKey == stock.StockKey);

            Stock.StockKey = stock.StockKey;
            Stock.StName = stock.StName;
            Stock.StSpec = stock.StSpec;
            Stock.Price = stock.Price;
            Stock.Num = stock.Num;

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public void DelStock(int StockKey)
        {
            CIMDataContext db = new CIMDataContext();

            Dat_Stock stock = db.Dat_Stock.Single(s => s.StockKey == StockKey);
            db.Dat_Stock.DeleteOnSubmit(stock);
            db.SubmitChanges();
        }

        //Export
        public static void DataToExcel(Hashtable QueryTable)
        {
            IQueryable<Dat_Stock> Query = new Stock().SelectStock(QueryTable);

            WebUtility.DelExport();

            string FileName = "~/export/" + Guid.NewGuid().ToString() + ".xls";
            string sNewFullFile = HttpContext.Current.Server.MapPath(FileName);

            try
            {
                File.Copy(HttpContext.Current.Server.MapPath("~/common/template.xls"), sNewFullFile);
            }
            catch (Exception er)
            {
                HttpContext.Current.Response.Write(er.Message);
                return;
            }

            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=True;Data Source=" + sNewFullFile + ";Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(strConn);
            OleDbCommand cmd = null;

            bool bRet = false;

            try
            {
                conn.Open();
                cmd = new OleDbCommand("create table [物品信息]([品名] Text,[规格] Text,[单价] Text,[数量] Text,[建立时间] Text,[建立人] Text)", conn);
                cmd.ExecuteNonQuery();


                string strSQL = "INSERT INTO [物品信息$] ([品名], [规格],[单价],[数量],[建立时间],[建立人]) VALUES (?,?,?,?,?,?)";
                cmd = new OleDbCommand(strSQL, conn);
                for (int i = 0; i < 6; i++)
                {
                    cmd.Parameters.Add(i.ToString(), OleDbType.VarChar);
                }
                foreach (Dat_Stock stock in Query)
                {
                    cmd.Parameters[0].Value = string.IsNullOrEmpty(stock.StName) ? "" : stock.StName;
                    cmd.Parameters[1].Value = string.IsNullOrEmpty(stock.StSpec) ? "" : stock.StSpec;
                    cmd.Parameters[2].Value = stock.Price == null ? "" : stock.Price.ToString();
                    cmd.Parameters[3].Value = stock.Num == null ? "" : stock.Num.ToString();
                    cmd.Parameters[4].Value = stock.CreateTime == null ? "" : stock.CreateTime.ToString();
                    cmd.Parameters[5].Value = stock.UserKey == null ? "" : new Account().GetAccountName(stock.UserKey);

                    cmd.ExecuteNonQuery();
                }
                bRet = true;
            }
            catch (Exception ex)
            {
                HttpContext.Current.Response.Write(ex.Message);

            }
            finally
            {
                if (cmd != null)
                    cmd.Dispose();
                conn.Dispose();
            }

            if (bRet)
                HttpContext.Current.Response.Redirect(FileName);
        }
    }

    public class TempStockKey
    {
        private int stockkey;
        public int StockKey
        {
           get { return stockkey; }
           set { stockkey = value; }
        }
    }

    public class InStock
    {
        public Dat_InStock SelectInStock(int InStockKey)
        {
            CIMDataContext db = new CIMDataContext();
            return db.Dat_InStock.Single(s => s.InStockKey == InStockKey);
        }

        //条件查询
        public IQueryable<Dat_InStock> SelectInStock(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_InStock> Query;

            if (QueryTable == null)
            {
                Query = from i in db.Dat_InStock
                        orderby i.InStockKey descending
                        select i;
            }
            else
            {
                Query = from i in GetConditionQuery(QueryTable)
                        orderby i.InStockKey descending
                        select i;
            }
            return Query;
        }

        private IQueryable<Dat_InStock> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            

            var predicate = PredicateBuilder.True<Dat_InStock>();

            if (QueryTable["StName"] != null)
            {
                predicate = predicate.And(i =>  ( from j in db.Dat_Stock
                                                  where j.StName.Contains(QueryTable["StName"].ToString())
                                                  select new TempStockKey
                                                  {
                                                     StockKey = j.StockKey
                                                  }
                                                ).Contains<TempStockKey>(new TempStockKey { StockKey = i.StockKey })
                                         );
            }
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And( i => ( from j in db.Dat_Stock
                                                  where j.StSpec.Contains(QueryTable["Keyword"].ToString()) ||
                                                  j.Price.ToString().Contains(QueryTable["Keyword"].ToString()) ||
                                                  j.Num.ToString().Contains(QueryTable["Keyword"].ToString())
                                                  select new TempStockKey
                                                  {
                                                     StockKey = j.StockKey
                                                  }
                                                ).Contains<TempStockKey>(new TempStockKey { StockKey = i.StockKey })
                                         );
            if (QueryTable["OneDate"] != null)
            {
                try
                {
                    DateTime OneDate = Convert.ToDateTime(QueryTable["OneDate"]);
                    predicate = predicate.And(i => i.CreateTime >= OneDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["TwoDate"] != null)
            {
                try
                {
                    DateTime TwoDate = Convert.ToDateTime(QueryTable["TwoDate"].ToString().Split(' ')[0] + " 23:59:59");
                    predicate = predicate.And(i => i.CreateTime <= TwoDate);
                }
                catch (Exception ex) { }
            }

            return db.Dat_InStock.Where(predicate);
        }

        public bool AddInStock(Dat_InStock instock)
        {
            CIMDataContext db = new CIMDataContext();

            db.Dat_InStock.InsertOnSubmit(instock);

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool EditInStock(Dat_InStock instock)
        {
            CIMDataContext db = new CIMDataContext();
            Dat_InStock InStock = db.Dat_InStock.Single(s => s.InStockKey == instock.InStockKey);

            InStock.Num = instock.Num;

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public void DelInStock(int InStockKey)
        {
            CIMDataContext db = new CIMDataContext();

            Dat_InStock instock = db.Dat_InStock.Single(i => i.InStockKey == InStockKey);
            db.Dat_InStock.DeleteOnSubmit(instock);
            db.SubmitChanges();
        }

        //获得流水号
        public string GetInStockNO()
        {
            CIMDataContext db = new CIMDataContext();

            string NO = "IN"+DateTime.Now.ToString("yyyyMMdd");

            var Query = (from i in db.Dat_InStock
                         orderby i.InStockNO descending
                         select i).Take(1);

            if (Query.Count() == 0)
            {
                return NO + "001";
            }
            else
            {
                string MaxNO = Query.First().InStockNO.Substring(10, 3);
                MaxNO = (Convert.ToInt32(MaxNO) + 1).ToString(); 

                if (MaxNO.Length < 3)
                {
                    int Length = MaxNO.Length;
                    for (int i = 0; i < 3 - Length; i++)
                    {
                        MaxNO = "0" + MaxNO;
                    }
                }

                return  NO + MaxNO;
            }

        }
    }

    public class OutStock
    {
        public Dat_OutStock SelectOutStock(int OutStockKey)
        {
            CIMDataContext db = new CIMDataContext();
            return db.Dat_OutStock.Single(s => s.OutStockKey == OutStockKey);
        }

        //条件查询
        public IQueryable<Dat_OutStock> SelectOutStock(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();

            IQueryable<Dat_OutStock> Query;

            if (QueryTable == null)
            {
                Query = from i in db.Dat_OutStock
                        orderby i.OutStockKey descending
                        select i;
            }
            else
            {
                Query = from i in GetConditionQuery(QueryTable)
                        orderby i.OutStockKey descending
                        select i;
            }
            return Query;
        }

        private IQueryable<Dat_OutStock> GetConditionQuery(Hashtable QueryTable)
        {
            CIMDataContext db = new CIMDataContext();



            var predicate = PredicateBuilder.True<Dat_OutStock>();

            if (QueryTable["StName"] != null)
            {
                predicate = predicate.And(i => (from j in db.Dat_Stock
                                                where j.StName.Contains(QueryTable["StName"].ToString())
                                                select new TempStockKey
                                                {
                                                    StockKey = j.StockKey
                                                }
                                                ).Contains<TempStockKey>(new TempStockKey { StockKey = i.StockKey })
                                         );
            }
            if (QueryTable["Keyword"] != null)
                predicate = predicate.And(i => (from j in db.Dat_Stock
                                                where j.StSpec.Contains(QueryTable["Keyword"].ToString()) ||
                                                j.Price.ToString().Contains(QueryTable["Keyword"].ToString()) ||
                                                j.Num.ToString().Contains(QueryTable["Keyword"].ToString())
                                                select new TempStockKey
                                                {
                                                    StockKey = j.StockKey
                                                }
                                                ).Contains<TempStockKey>(new TempStockKey { StockKey = i.StockKey })
                                         );
            if (QueryTable["OneDate"] != null)
            {
                try
                {
                    DateTime OneDate = Convert.ToDateTime(QueryTable["OneDate"]);
                    predicate = predicate.And(i => i.CreateTime >= OneDate);
                }
                catch (Exception ex) { }
            }
            if (QueryTable["TwoDate"] != null)
            {
                try
                {
                    DateTime TwoDate = Convert.ToDateTime(QueryTable["TwoDate"].ToString().Split(' ')[0] + " 23:59:59");
                    predicate = predicate.And(i => i.CreateTime <= TwoDate);
                }
                catch (Exception ex) { }
            }

            return db.Dat_OutStock.Where(predicate);
        }

        public bool AddOutStock(Dat_OutStock outstock)
        {
            CIMDataContext db = new CIMDataContext();

            db.Dat_OutStock.InsertOnSubmit(outstock);

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {   
                return false;
            }
        }

        public bool EditOutStock(Dat_OutStock outstock)
        {

            CIMDataContext db = new CIMDataContext();
            Dat_OutStock OutStock = db.Dat_OutStock.Single(s => s.OutStockKey == outstock.OutStockKey);

            OutStock.DanNum = outstock.DanNum;
            OutStock.Num = outstock.Num;
            OutStock.SalesCustomer = outstock.SalesCustomer;
            OutStock.SalesPrice = outstock.SalesPrice;
            OutStock.SalesAccountType = outstock.SalesAccountType;
            OutStock.OtherReason = outstock.OtherReason;

            try
            {  
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        //修改出库单预约单号
        public bool EditOutStockNO(Dat_OutStock outstock)
        {

            CIMDataContext db = new CIMDataContext();
            Dat_OutStock OutStock = db.Dat_OutStock.Single(s => s.OutStockKey == outstock.OutStockKey);

            OutStock.DanNum = outstock.DanNum;
          
            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public void DelOutStock(int OutStockKey)
        {
            CIMDataContext db = new CIMDataContext();

            Dat_OutStock outstock = db.Dat_OutStock.Single(i => i.OutStockKey == OutStockKey);
            db.Dat_OutStock.DeleteOnSubmit(outstock);
            db.SubmitChanges();
        }

        //获得流水号
        public string GetOutStockNO()
        {
            CIMDataContext db = new CIMDataContext();

            string NO = "OUT"+DateTime.Now.ToString("yyyyMMdd");

            var Query = (from i in db.Dat_OutStock
                         orderby i.OutStockNO descending
                         select i).Take(1);

            if (Query.Count() == 0)
            {
                return NO + "001";
            }
            else
            {
                string MaxNO = Query.First().OutStockNO.Substring(11, 3);
                MaxNO = ( Convert.ToInt32(MaxNO) + 1).ToString(); 

                if (MaxNO.Length < 3)
                {
                    int Length = MaxNO.Length;
                    for (int i = 0; i < 3 - Length; i++)
                    {
                        MaxNO = "0" + MaxNO;
                    }
                }

                return NO + MaxNO;
            }

        }
        
    }
}
